sql基础语句笔记
1、将表中某一字段的最大值取出来的方式:
SELECT MAX(contract_no) FROM contract_c WHERE contract_no LIKE '%JK%'
2、在查看合同(contract_c)时,查看合同对应的货物(contract_product_c)数量和货物对应附件(ext_cproduct_c)的数量。
合同表的ID是货物表的外键,货物ID是附件的外键。是两层一对多的关系。
SELECT contract_no,
(
SELECT COUNT(*) FROM contract_product_c
WHERE contract_id = c.CONTRACT_ID
)cpnum
FROM contract_c c
WHERE contract_id='4028817a3357462e01336d3a0be10014'
SELECT COUNT(*) FROM ext_cproduct_c WHERE contract_product_id IN(
SELECT contract_product_id FROM contract_product_c
WHERE contract_id ='4028817a3357462e01336d3a0be10014'
)
SELECT
(
SELECT COUNT(*) FROM contract_product_c WHERE contract_id = c.contract_id
)
cpnum,
(
SELECT COUNT(*) FROM ext_cproduct_c WHERE contract_product_id IN(
SELECT contract_product_id WHERE contract_id = c.contract_id
)
)
epnum
,c.*
FROM contract_c c
3、查询合同(contract_c)对应的货物(contract_product_c)的数量(cnumber)和价格(price)的乘积
SELECT
c.contract_no,
cp.cpnum,
cp.cptotal
FROM
(
SELECT * FROM contract_c
)c
LEFT JOIN
(
SELECT contract_id,COUNT(*) AS cpnum,SUM(cnumber*price) AS cptotal FROM contract_product_c
GROUP BY contract_id
)cp
ON c.contract_id = cp.contract_id
4、
SELECT c.contract_no,
t.factory_name,t.product_no,
t.ext_factory_name,t.ext_product_no
FROM
(SELECT contract_id,contract_no FROM contract_c)c
LEFT JOIN
(SELECT cp.contract_id,
cp.factory_name,cp.product_no,
ep.factory_name AS ext_factory_name,ep